package com.mvc.framework.service;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Entity;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.transaction.annotation.Transactional;

import com.mvc.framework.config.ProjectConfig;
import com.mvc.framework.dao.BaseHibernateDao;
import com.mvc.framework.dao.HibernateIdUtils;
import com.mvc.framework.dao.HibernateWebUtils;
import com.mvc.framework.model.Auditable;
import com.mvc.framework.model.CommonAuditableEntity;
import com.mvc.framework.model.CompositeKeyAuditableEntity;
import com.mvc.framework.model.ObjectCount;
import com.mvc.framework.util.FrameworkContextUtils;
import com.mvc.framework.util.HibernateUtils;
import com.mvc.framework.util.PageSearch;

/**
 * Basic Service
 * Support CRUD, page query and common search function
 * @author pubx 2010-3-29 02:26:46
 */
public abstract class BaseService<T, PK extends Serializable> implements PageManager<T> {
	private static final Logger LOGGER = Logger.getLogger(BaseService.class);

	@Autowired
	private BaseHibernateDao<T, Serializable> baseHibernateDao;
	
	public static boolean canChangeIsolation  = true;

	public T load(Serializable id) {
		return get(id);
	}
	
	/**
	 * Get by ID
	 */
	public T get(Serializable id) {
		Class entityClass = getActualArgumentType();
		String sql = "SELECT A FROM  " + entityClass.getName() + " A WHERE A.id = " + id;
		List<T> list = searchBySql(entityClass, sql);
		if(list.size()>0){
			return list.get(0);
		}
		return null;
	}

	public HibernateTemplate getHibernateTemplate() {
		return baseHibernateDao.getHibernateTemplate();
	}

	protected Class getActualArgumentType() {
		return (Class)((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
	}
	@Transactional
	public void delete(Serializable id) {
		delete(get(id));
	}
	@Transactional
	public void delete(T entity) {
		baseHibernateDao.delete(entity);
	}
	
	/**
	 * Save entity
	 */
	public void save(T entity) {
		if (entity instanceof CommonAuditableEntity) {
			CommonAuditableEntity auditableEntity = (CommonAuditableEntity)entity;
			if(null==auditableEntity.getObjectId()){
				auditableEntity.setObjectId(createSystemId());
			}
		}
		baseHibernateDao.save(entity);
	}
	
	/**
	 * Page query
	 */
	public void find(final PageSearch page) {
		getHibernateTemplate().execute(new HibernateCallback<PageSearch>() {
			public PageSearch doInHibernate(Session session) {
				tryChangeIsolationForSqlServer(session);
				Criteria criteria = HibernateWebUtils.createCriteria(session, page.getEntityClass(), page.getFilters());
				if (0 == page.getTotalCount()) {
					criteria.setProjection(Projections.rowCount());
					Object object = criteria.uniqueResult();
					int total = object == null ? 0 : ((Number) object).intValue();
					page.setTotalCount(total);
				}
				addOrder(page, criteria);
				criteria.setProjection(null);
				List items = criteria.setFirstResult(page.getBegin()).setMaxResults(page.getPageSize()).list();
				page.setList(items);
				return page;
			}

		});
	}
	
	protected void addOrder(final PageSearch page, Criteria criteria) {
        if (page.getOrders() != null) {
			for(Order order : page.getOrders()){
				criteria.addOrder(order);
			}
		}
    }
	//SQL server only
	protected void tryChangeIsolationForSqlServer(Session session) {
		if(canChangeIsolation){
			try {
				session.connection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
	        } catch (Exception e) {
	        	canChangeIsolation = false;
	        	LOGGER.debug("tryChangeIsolationForSqlServer",e);
	        }
		}
    }
	
	public Integer createSystemId() {
		return createSystemId(getActualArgumentType());
	}
	
	public Integer createSystemId(Class entity) {
		return HibernateIdUtils.getObjectId(getHibernateTemplate().getSessionFactory(), entity);
	}

	@Transactional
	public int deleteByWhere(String where) {
		return deleteEntitiesByWhere(getActualArgumentType(), where);
	}
	@Transactional
	public int deleteEntitiesByWhere(Class entity, String where) {
		if(CompositeKeyAuditableEntity.class.isAssignableFrom(entity)){
			saveHistory(entity, where);
		}
		StringBuilder hql = new StringBuilder();
		hql.append("DELETE ").append(entity.getName()).append(" WHERE ").append(where);
		Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		Query query = session.createQuery(hql.toString());
		int result = query.executeUpdate();
		return result;
	}

	@Transactional
	public int disableByWhere(String where) {
		return disableEntitiesByWhere(getActualArgumentType(), where);
	}
	
	@Transactional
	private void saveHistory(Class entity, String where){
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		Entity entityClass =(Entity)entity.getAnnotation(Entity.class);
		// Set update user and date
		String updateHql = "update " + entityClass.name() + " set updatedOn=?, updatedBy=?,recStatus=? where " + where; 
		Query update = session.createQuery(updateHql.toString()).setParameter(0, new Date()).
				setParameter(1, FrameworkContextUtils.getCurrentUserId()).setParameter(2, Auditable.REC_STATUS_INVALID);
		update.executeUpdate();
		// Save to history table
		String queryHql = " SELECT A from " + entityClass.name() + " A where " + where;
    	String querySql = HibernateUtils.hqlToSql(sessionFactory, queryHql);
    	StringBuilder sql = new StringBuilder(40);
    	sql.append("INSERT INTO ").append(entityClass.name()).append(ProjectConfig.HISTORY_TABLE_SUFFIX).append(" ").append(querySql);
		SQLQuery query = session.createSQLQuery(sql.toString());
		query.executeUpdate();
	}
	
	@Transactional
	public int disableEntitiesByWhere(Class entity, String where) {
		StringBuilder hql = new StringBuilder(30);
		hql.append("UPDATE ").append(entity.getName()).append(" SET recStatus = ? and updatedOn=? and updatedBy=?").append(" WHERE ").append(where);
		Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		Query query = session.createQuery(hql.toString());
		query.setParameter(0, Auditable.REC_STATUS_INVALID);
		query.setParameter(1, new Date());
		query.setParameter(2, FrameworkContextUtils.getCurrentUserId());
		return query.executeUpdate();
	}

	// ==== search by SQL (return as List<T> or T)
	/**
	 * Search by HQL and get object list.
	 * 
	 * @param sql
	 * @return
	 */
	public List<T> searchBySql(String sql) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		tryChangeIsolationForSqlServer(session);
		Query query = session.createQuery(sql);
		List result = query.list();
		session.close();
		return result;
	}

	/**
	 * Search by HQL with prepared parameters.
	 * 
	 * @param entity
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<T> searchBySql(String sql, Object... params) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		tryChangeIsolationForSqlServer(session);
		Query query = session.createQuery(sql);
		int i = 0;
		for (Object object : params) {
			query.setParameter(i++, object);
		}
		List result = query.list();
		session.close();
		return result;
	}
	
	/**
	 * Search by HQL and return object of specific type - T.
	 * 
	 * @param entity
	 * @param sql
	 * @return
	 */
	public T searchObjectBySql(String sql) {
		List<T> result = this.searchBySql(sql);
		if (result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	/**
	 * Search by HQL with prepared parameters.
	 * 
	 * @param entity
	 * @param sql
	 * @param params
	 * @return
	 */
	public T searchObjectBySql(String sql, Object... params) {
		List<T> result = this.searchBySql(sql, params);
		if (result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	// ==== search by SQL (return as List<A> or A)

	/**
	 * Search By HQL and return List of specified type (entity).
	 * 
	 * @param entity
	 * @param sql
	 * @return
	 */
	private <A> List<A> searchBySql(Class<A> entity, String sql) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		tryChangeIsolationForSqlServer(session);
		Query query = session.createQuery(sql);
		List<A> result = query.list();
		session.close();
		return result;
	}
	
	/**
	 * @param entity
	 * @param sql
	 * @param params
	 * @return
	 */
	public <A> List<A> searchBySql(Class<A> entity, String sql, Object... params) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		tryChangeIsolationForSqlServer(session);
		Query query = session.createQuery(sql);
		int i = 0;
		for (Object object : params) {
			query.setParameter(i++, object);
		}
		List result = query.list();
		session.close();
		return result;
	}

	/**
	 * @param entity
	 * @param sql
	 * @param params
	 * @return
	 */
	public <A> A searchObjectBySql(Class<A> entity, String sql, Object... params) {
		List<A> result = this.searchBySql(entity, sql, params);
		if (result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	/**
	 * @param entity
	 * @param sql
	 * @return
	 */
	public <A> A searchObjectBySql(Class<A> entity, String sql) {
		List<A> result = this.searchBySql(entity, sql);
		if (result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	// ==== search by Native SQL (return as List or Object)

	/**
	 * @param entity
	 * @param sql
	 * @return
	 */
	public <A> List<A> searchByNativeSql(Class<A> entity, String sql) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		tryChangeIsolationForSqlServer(session);
		Query query = session.createSQLQuery(sql).addEntity(entity);
		List<A> result = query.list();
		session.close();
		return result;
	}
	
	
	/**
	 * @param entity
	 * @param sql
	 * @param params
	 * @return
	 */
	public <A> List<A> searchByNativeSql(Class<A> entity, String sql, Object... params) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		tryChangeIsolationForSqlServer(session);
		Query query = session.createSQLQuery(sql).addEntity(entity);
		int i = 0;
		for (Object object : params) {
			query.setParameter(i++, object);
		}
		List<A> result = query.list();
		session.close();
		return result;	
	}
	
	/**
	 * 
	 * @param entity
	 * @param sql
	 * @return
	 */
	public <A> A searchObjectByNativeSql(Class<A> entity, String sql) {
		List<A> result = this.searchByNativeSql(entity, sql);
		if (result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	/**
	 * @param entity
	 * @param sql
	 * @param params
	 * @return
	 */
	public <A> A searchObjectByNativeSql(Class<A> entity, String sql, Object... params) {
		List<A> result = this.searchByNativeSql(entity, sql, params);
		if (result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	/**
	 * Search by where
	 * @param where the condition after the where
 	 * @return
	 */
	public T searchByWhere(String where) {
		Class entityClass = getActualArgumentType();
		String sql = "SELECT A FROM  " + entityClass.getName() + " A WHERE " + where;
		List<T> list = searchBySql(entityClass, sql);
		if(list.size()>0){
			return list.get(0);
		}
		return null;
	}
	@Transactional
	public int truncate(){
		return truncateEntiry(getActualArgumentType());
	}
	@Transactional
	public int truncateEntiry(Class entity) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		Entity entityClass =(Entity)entity.getAnnotation(Entity.class);
		String hql = "TRUNCATE TABLE " + entityClass.name();
		Query query = session.createSQLQuery(hql);
		int result = query.executeUpdate();
		session.close();
		return result;
	}
	
	/**
	 * Get object by objectId 
	 * @param objectId
	 * @return
	 */
	public T getByObjectId(Integer objectId){
		if(null==objectId){
			return null;
		}
		Class entityClass = getActualArgumentType();
		String sql = "SELECT A FROM  " + entityClass.getName() + " A WHERE A.objectId = " + objectId + " and recStatus = " + Auditable.REC_STATUS_VALID;
		List<T> list = searchBySql(entityClass, sql);
		if(list.size()>0){
			return list.get(0);
		}
		return null;
	}
	
	/**
	 * Get last Invalid Object 
	 * @param objectId
	 * @return
	 */
	public T getLastInvalidByObjectId(Long objectId){
		if(null==objectId){
			return null;
		}
		Class entityClass = getActualArgumentType();
		String sql = "SELECT A FROM  " + entityClass.getName() + " A WHERE A.objectId = " + objectId + " and recStatus = " + Auditable.REC_STATUS_INVALID + " order by createdOn desc";
		List<T> list = searchBySql(entityClass, sql);
		if(list.size()>0){
			return list.get(0);
		}
		return null;
	}
	
	/**
	 * 获得所有对象的历史记录个数
	 * 
	 * @return
	 */
	public List<ObjectCount> getAllObjectCount(){
		Class entityClass = getActualArgumentType();
		String sql = "SELECT objectId,count(*) FROM  " + entityClass.getName() + " A group by objectId";
		List<Object[]> list = (List<Object[]>) searchBySql(Object[].class, sql);
		List<ObjectCount> result = new ArrayList<ObjectCount>(list.size());
		for(Object[] objects : list){
			result.add(new ObjectCount((Long)objects[0], (Long)objects[1]));
		}
		return result;
	}
}
